﻿using AutoMapper;
using Dapper;
using DocumentFormat.OpenXml.Bibliography;
using DocumentFormat.OpenXml.Drawing;
using DocumentFormat.OpenXml.Spreadsheet;
using DocumentFormat.OpenXml.Wordprocessing;
using Hangfire.LiteDB;
using housingsafety.server.Caching.Core;
using housingsafety.server.Entities;
using housingsafety.server.Entities.DataModels;
using housingsafety.server.FastDBF;
using housingsafety.server.Helpers;
using housingsafety.server.Models;
using housingsafety.server.Repository;
using housingsafety.server.Repository.Extensions;
using housingsafety.server.Svcs;
using Microsoft.AspNetCore.Http;
using Microsoft.AspNetCore.Mvc;
using Microsoft.AspNetCore.WebUtilities;
using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Logging;
using Microsoft.Net.Http.Headers;
using MySql.Data.MySqlClient;
using NetTopologySuite.Geometries;
using NetTopologySuite.IO;
using Newtonsoft.Json;
using Npgsql;
using NPOI.SS.Formula.Functions;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using Quartz.Util;
using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.IO;
using System.Linq;
using System.Net;
using System.Reflection;
using System.Text;
using System.Text.Json;
using System.Text.RegularExpressions;
using System.Threading.Tasks;
using System.Transactions;
using static housingsafety.server.Controllers.VillageMngController;

namespace housingsafety.server.Controllers
{
    /// <summary>
    /// 村庄管理服务
    /// </summary>
    public class basicHouseController : BaseController<VillageMngController>
    {
        private readonly ICache _cache;
        private readonly IMapper _mapper;
        private readonly IStorageProvider _storageProvider;
        private readonly ZmDbContext _dbContext;
        private readonly IRepository<ZmUser> _userRepository;
        private readonly IRepository<ZmBuildingsLayer> _buildingsRepository;
        private readonly IRepository<ZmFileAttach> _fileAttachRepository;
        private readonly IRepository<ZmVillageFireSafety> _villageFireSafetyRepository;

        public basicHouseController(IStorageProvider storageProvider
            , IRepository<ZmUser> userRepository
            , IRepository<ZmBuildingsLayer> buildingsRepository
            , IRepository<ZmFileAttach> fileAttachRepository
            , IRepository<ZmVillageFireSafety> villageFireSafetyRepository
            , IMapper mapper, ICacheProvider cacheProvider, ZmDbContext dbContext
            , ILogger<VillageMngController> logger
            , IRepository<ZmSystemLog> systemLogRepository) : base(logger, systemLogRepository)
        {
            _storageProvider = storageProvider;
            _mapper = mapper;
            _dbContext = dbContext;
            _cache = cacheProvider.CreateCache();

            _userRepository = userRepository;
            _buildingsRepository = buildingsRepository;
            _fileAttachRepository = fileAttachRepository;
            _villageFireSafetyRepository = villageFireSafetyRepository;
        }

        /// <summary>
        /// 房屋基本信息情况————根据行政区划统计基本信息情况
        /// </summary>
        /// <param name="pageRequest"></param>
        /// <returns></returns>
        [HttpGet("basicHouse/basicHouseGqList")]
        [ProducesResponseType(typeof(RespRet<Page<object>>), 200)]
        public RespRet<Page<object>> basicHouseGqList([FromQuery] PagePcztRequestInput pageRequest)
        {
            try
            {

                if (pageRequest.PageIndex < 0)
                {
                    return new RespRet<Page<object>>(false, "PageIndex must >= 0");
                }

                var userNumber = this.CurrentUser();
                if (string.IsNullOrEmpty(userNumber))
                {
                    return new RespRet<Page<object>>(false, "user not logined");
                }

                string strWhere = " ";
                var user = _userRepository.FirstOrDefault(u => u.Number == userNumber && u.IsDelete != 1);
                if (null == user)
                {
                    return new RespRet<Page<object>>(false, "用户名不存在");
                }
                string district = user.District;
                var options = new JsonSerializerOptions();
                options.PropertyNameCaseInsensitive = true; // 设置属性不区分大小写
                Xzq model = System.Text.Json.JsonSerializer.Deserialize<Xzq>(district, options);


                if (model != null)
                {
                    if (user.RoleType == 3) //区级
                    {
                        strWhere += " and xzqdm='" + model.RegionCode + "'";
                    }
                }
                string today = DateTime.Now.ToString("yyyy-MM-dd");
                string table_fwzt = "";
                if (!string.IsNullOrEmpty(pageRequest.endtime))
                {
                    DateTime endData = Convert.ToDateTime(pageRequest.endtime);
                    table_fwzt = "zm_building_fwzt_" + endData.Year;
                    if (endData > DateTime.Now)
                    {
                        table_fwzt = "zm_building_fwzt_" + endData.Year;
                    }
                }
                else
                {
                    DateTime endData = Convert.ToDateTime(today);
                    table_fwzt = "zm_building_fwzt_" + endData.Year;
                }



                string sql = "";
                if (pageRequest.startime == "2024-01-01" && pageRequest.endtime == today)
                {
                    sql = @"select ROW_NUMBER() OVER ()index ,* from (select t1.*,t2.""orderIdx"" from pro_hoursejbxx_gq('" + pageRequest.startime + "', ' " + pageRequest.endtime + " 23:59:59', 'zm_pc_base', 'zm_pc_yh','" + table_fwzt + "') t1 left join zm_dic_xzq t2 on t1.xzqdm=t2.xzqdm order by t2.\"orderIdx\" ) t where 1=1 " + strWhere + " ";
                }
                else
                {
                    sql = @"select ROW_NUMBER() OVER ()index ,* from (select t1.*,t2.""orderIdx"" from pro_hoursejbxx_gq('" + pageRequest.startime + "','" + pageRequest.endtime + " 23:59:59', 'zm_pc_baselog', 'zm_pc_yhlog','" + table_fwzt + "') t1 left join zm_dic_xzq t2 on t1.xzqdm=t2.xzqdm order by t2.\"orderIdx\" ) t where 1=1 " + strWhere + "  ";
                }
                if (pageRequest.cked == "true")
                {
                    if (pageRequest.startime == "2024-01-01" && pageRequest.endtime == today)
                    {
                        sql = @"select ROW_NUMBER() OVER ()index ,* from (select xzq,xzqdm,sum(zcount) zcount,sum(jzrks) jzrks,sum(zzrs) zzrs,sum(fjsl) fjsl,sum(jzfjs) jzfjs,sum(one) one,sum(two) two,sum(three) three 
                    from pro_hoursejbxx_cj('" + pageRequest.startime + "', '" + pageRequest.endtime + " 23:59:59', 'zm_pc_base', 'zm_pc_yh','" + table_fwzt + "')  where villagedm in(select villagedm from  zdcmd) group by xzq,xzqdm order by xzqdm) t  ";
                    }
                    else
                    {
                        sql = @"select ROW_NUMBER() OVER ()index ,* from (select xzq,xzqdm,sum(zcount) zcount,sum(jzrks) jzrks,sum(zzrs) zzrs,sum(fjsl) fjsl,sum(jzfjs) jzfjs,sum(one) one,sum(two) two,sum(three) three 
                    from pro_hoursejbxx_cj('" + pageRequest.startime + "', '" + pageRequest.endtime + " 23:59:59', 'zm_pc_baselog', 'zm_pc_yhlog','" + table_fwzt + "')  where villagedm in(select villagedm from  zdcmd) group by xzq,xzqdm order by xzqdm) t  ";

                    }
                }

                var list = _dbContext.Database.GetDbConnection().Query<object>(sql).ToList();
                int count = list.Count;

                var pageData = new Page<object>(list
                    , pageRequest.PageIndex + 1, pageRequest.Limit, count);
                return new RespRet<Page<object>>(true, "Ok", pageData);

            }
            catch (Exception ex)
            {
                return new RespRet<Page<object>>(false, "语句连接超时");
            }
        }
        /// <summary>
        /// 房屋基本信息情况————根据乡镇统计基本信息
        /// </summary>
        /// <param name="pageRequest"></param>
        /// <returns></returns>
        [HttpGet("basicHouse/basicHouseTownList")]
        public RespRet<Page<object>> basicHouseTownList([FromQuery] string xzqdm, [FromQuery] int pageindex, [FromQuery] int pagesize, [FromQuery] string startTime, [FromQuery] string endTime, [FromQuery] string cked)
        {
            if (pageindex < 0)
            {
                return new RespRet<Page<object>>(false, "PageIndex must >= 0");
            }


            var userNumber = this.CurrentUser();
            if (string.IsNullOrEmpty(userNumber))
            {
                return new RespRet<Page<object>>(false, "user not logined");
            }

            string strWhere = " ";
            var user = _userRepository.FirstOrDefault(u => u.Number == userNumber && u.IsDelete != 1);
            if (null == user)
            {
                return new RespRet<Page<object>>(false, "用户名不存在");
            }
            string district = user.District;
            var options = new JsonSerializerOptions();
            options.PropertyNameCaseInsensitive = true; // 设置属性不区分大小写
            Xzq model = System.Text.Json.JsonSerializer.Deserialize<Xzq>(district, options);


            if (model != null)
            {

                if (user.RoleType == 1 || user.RoleType == 2)
                {

                    strWhere += "and  xzq!='东城区' and xzq!='西城区'";
                }
                else if (user.RoleType == 3) //区级
                {
                    xzqdm = model.RegionCode;
                    strWhere += " and xzqdm='" + xzqdm + "'";
                }
                else  //乡镇
                {
                    string strtowndm = model.RegionCode;
                    if (model.RegionCode.Length != 12)
                    {
                        strtowndm += "000";
                    }

                    strWhere += "and xzqdm='" + xzqdm + "' and towndm='" + strtowndm + "'";
                }
            }
            if (string.IsNullOrEmpty(startTime) && string.IsNullOrEmpty(endTime))
            {
                startTime = "2024-01-01";
                endTime = DateTime.Now.ToString("yyyy-MM-dd");
            }
            if (!string.IsNullOrEmpty(xzqdm))
            {
                strWhere += " and xzqdm='" + xzqdm + "'";
            }

            string sql = ""; string sql_count = "";
            string today = DateTime.Now.ToString("yyyy-MM-dd");
            string table_fwzt = "";
            if (!string.IsNullOrEmpty(endTime))
            {
                DateTime endData = Convert.ToDateTime(endTime);
                table_fwzt = "zm_building_fwzt_" + endData.Year;
                if (Convert.ToDateTime(endTime) > DateTime.Now)
                {
                    table_fwzt = "zm_building_fwzt_" + DateTime.Now.Year;
                }
            }
            else
            {
                DateTime endData = Convert.ToDateTime(today);
                table_fwzt = "zm_building_fwzt_" + endData.Year;
            }
            if (startTime == "2024-01-01" && endTime == today)
            {
                sql = @"select ROW_NUMBER() OVER ()index ,* from (select * from pro_hoursejbxx_xz('" + startTime + "', ' " + endTime + " 23:59:59', 'zm_pc_base', 'zm_pc_yh','" + table_fwzt + "') order by towndm) t  where 1=1 " + strWhere + "  ";
            }
            else
            {
                sql = @"select ROW_NUMBER() OVER ()index ,* from (select * from pro_hoursejbxx_xz('" + startTime + "','" + endTime + " 23:59:59', 'zm_pc_baselog', 'zm_pc_yhlog','" + table_fwzt + "') order by towndm) t  where 1=1 " + strWhere + "  ";
            }
            if (cked == "true")
            {
                if (startTime == "2024-01-01" && endTime == today)
                {
                    sql = @"select ROW_NUMBER() OVER ()index ,* from (select xzq,xzqdm,town,towndm,sum(zcount) zcount,sum(jzrks) jzrks,sum(zzrs) zzrs,sum(fjsl) fjsl,sum(jzfjs) jzfjs,sum(one) one,sum(two) two,sum(three) three 
                    from pro_hoursejbxx_cj('" + startTime + "', '" + endTime + " 23:59:59', 'zm_pc_base', 'zm_pc_yh','" + table_fwzt + "')  where villagedm in(select villagedm from  zdcmd) group by xzq,xzqdm,town,towndm order by xzqdm) t  where 1=1 " + strWhere + "  ";
                }
                else
                {
                    sql = @"select ROW_NUMBER() OVER ()index ,* from (select xzq,xzqdm,town,towndm,sum(zcount) zcount,sum(jzrks) jzrks,sum(zzrs) zzrs,sum(fjsl) fjsl,sum(jzfjs) jzfjs,sum(one) one,sum(two) two,sum(three) three 
                    from pro_hoursejbxx_cj('" + startTime + "', '" + endTime + " 23:59:59', 'zm_pc_baselog', 'zm_pc_yhlog','" + table_fwzt + "')  where villagedm in(select villagedm from  zdcmd) group by xzq,xzqdm,town,towndm order by towndm) t   where 1=1 " + strWhere + "  ";

                }
            }

            if (sql.Contains("insert into", StringComparison.OrdinalIgnoreCase)
                || sql.Contains("delete from", StringComparison.OrdinalIgnoreCase)
                || sql.Contains("update ", StringComparison.OrdinalIgnoreCase))
            {
                return new RespRet<Page<object>>(false, "不符合规范，禁止执行");
            }
            if (sql_count.Contains("insert into", StringComparison.OrdinalIgnoreCase)
                || sql_count.Contains("delete from", StringComparison.OrdinalIgnoreCase)
                || sql_count.Contains("update ", StringComparison.OrdinalIgnoreCase))
            {
                return new RespRet<Page<object>>(false, "不符合规范，禁止执行");
            }


            int count = _dbContext.Database.GetDbConnection().QueryFirstOrDefault<int>(sql_count);
            var datas = _dbContext.Database.GetDbConnection().Query<object>(sql).ToList();
            return new RespRet<Page<object>>(true, "ok", new Page<object> { Items = datas, NextPageIndex = pageindex + 1, PageTotal = count, Total = count });

        }
        /// <summary>
        /// 房屋基本信息情况————根据村级统计基本信息
        /// </summary>
        /// <param name="pageRequest"></param>
        /// <returns></returns>
        [HttpGet("basicHouse/basicHouseVillageList")]
        public RespRet<Page<object>> basicHouseVillageList([FromQuery] string xzqdm, [FromQuery] string towndm, [FromQuery] int pageindex, [FromQuery] int pagesize, [FromQuery] string startTime, [FromQuery] string endTime, [FromQuery] string cked)
        {
            if (pageindex < 0)
            {
                return new RespRet<Page<object>>(false, "PageIndex must >= 0");
            }
            var userNumber = this.CurrentUser();
            if (string.IsNullOrEmpty(userNumber))
            {
                return new RespRet<Page<object>>(false, "user not logined");
            }
            string strWhere = "";
            if (!string.IsNullOrEmpty(xzqdm + ""))
            {
                strWhere += "and xzqdm ='" + xzqdm + "'";
            }
            if (!string.IsNullOrEmpty(towndm + ""))
            {
                strWhere += "and towndm ='" + towndm + "'";
            }
            if (string.IsNullOrEmpty(startTime))
            {
                startTime = "2024-01-01";
                endTime = DateTime.Now.ToString("yyyy-MM-dd");
            }

            if (!string.IsNullOrEmpty(xzqdm) && !string.IsNullOrEmpty(towndm))
            {
                strWhere += " and xzqdm='" + xzqdm + "' and towndm='" + towndm + "'";
            }

            string sql = ""; string sql_count = "";
            string today = DateTime.Now.ToString("yyyy-MM-dd");
            string table_fwzt = "";
            if (!string.IsNullOrEmpty(endTime))
            {
                DateTime endData = Convert.ToDateTime(endTime);
                table_fwzt = "zm_building_fwzt_" + endData.Year;
                if (Convert.ToDateTime(endTime) > DateTime.Now)
                {
                    table_fwzt = "zm_building_fwzt_" + DateTime.Now.Year;
                }
            }
            else
            {
                DateTime endData = Convert.ToDateTime(today);
                table_fwzt = "zm_building_fwzt_" + endData.Year;
            }
            if (startTime == "2024-01-01" && endTime == today)
            {
                sql = @"select ROW_NUMBER() OVER ()index ,* from (select * from pro_hoursejbxx_cj('" + startTime + "', ' " + endTime + " 23:59:59', 'zm_pc_base', 'zm_pc_yh','" + table_fwzt + "') order by villagedm) t  where 1=1 " + strWhere + "  ";
            }
            else
            {
                sql = @"select ROW_NUMBER() OVER ()index ,* from (select * from pro_hoursejbxx_cj('" + startTime + "','" + endTime + " 23:59:59', 'zm_pc_baselog', 'zm_pc_yhlog','" + table_fwzt + "') order by villagedm) t  where 1=1 " + strWhere + "  ";
            }
            if (cked == "true")
            {
                if (startTime == "2024-01-01" && endTime == today)
                {
                    sql = @"select ROW_NUMBER() OVER ()index ,* from (select *
                    from pro_hoursejbxx_cj('" + startTime + "', '" + endTime + " 23:59:59', 'zm_pc_base', 'zm_pc_yh','" + table_fwzt + "')  where villagedm in(select villagedm from  zdcmd)  order by xzqdm) t  where 1=1 " + strWhere + "  ";
                }
                else
                {
                    sql = @"select ROW_NUMBER() OVER ()index ,* from (select * 
                    from pro_hoursejbxx_cj('" + startTime + "', '" + endTime + " 23:59:59', 'zm_pc_baselog', 'zm_pc_yhlog','" + table_fwzt + "')  where villagedm in(select villagedm from  zdcmd)  order by villagedm) t   where 1=1 " + strWhere + "  ";

                }
            }
            if (sql.Contains("insert into", StringComparison.OrdinalIgnoreCase)
                || sql.Contains("delete from", StringComparison.OrdinalIgnoreCase)
                || sql.Contains("update ", StringComparison.OrdinalIgnoreCase))
            {
                return new RespRet<Page<object>>(false, "不符合规范，禁止执行");
            }


            if (sql.Contains("insert into", StringComparison.OrdinalIgnoreCase)
              || sql.Contains("delete from", StringComparison.OrdinalIgnoreCase)
              || sql.Contains("update ", StringComparison.OrdinalIgnoreCase))
            {
                return new RespRet<Page<object>>(false, "不符合规范，禁止执行");
            }
            if (sql_count.Contains("insert into", StringComparison.OrdinalIgnoreCase)
                || sql_count.Contains("delete from", StringComparison.OrdinalIgnoreCase)
                || sql_count.Contains("update ", StringComparison.OrdinalIgnoreCase))
            {
                return new RespRet<Page<object>>(false, "不符合规范，禁止执行");
            }


            int count = _dbContext.Database.GetDbConnection().QueryFirstOrDefault<int>(sql_count);
            var datas = _dbContext.Database.GetDbConnection().Query<object>(sql).ToList();
            return new RespRet<Page<object>>(true, "ok", new Page<object> { Items = datas, NextPageIndex = pageindex + 1, PageTotal = count, Total = count });

        }

        /// <summary>
        /// 导出基本信息统计明细（各区）
        /// </summary>
        /// <param name="pageRequest">type check</param>
        /// <returns></returns>
        [HttpGet("basicHouse/export/basicHouseGqListExport")]
        [Produces("application/octet-stream")]
        //[ProducesResponseType(200)]
        //[ProducesResponseType(401)]
        public IActionResult basicHouseGqListExport([FromQuery] string startTime, [FromQuery] string endTime, [FromQuery] string cked)
        {
            try
            {

                var userNumber = this.CurrentUser();
                string strWhere = "";
                var user = _userRepository.FirstOrDefault(u => u.Number == userNumber && u.IsDelete != 1);
                string district = user.District;
                var options = new JsonSerializerOptions();
                options.PropertyNameCaseInsensitive = true; // 设置属性不区分大小写
                Xzq model = System.Text.Json.JsonSerializer.Deserialize<Xzq>(district, options);


                if (model != null)
                {
                    if (user.RoleType == 1 || user.RoleType == 2)
                    {
                        strWhere += "and  xzq!='东城区' and xzq!='西城区'";
                    }
                    else if (user.RoleType == 3) //区级
                    {
                        strWhere += " and xzqdm='" + model.RegionCode + "'";
                    }
                }
                string today = DateTime.Now.ToString("yyyy-MM-dd");
                string sql = "";
                string table_fwzt = "";
                if (!string.IsNullOrEmpty(endTime))
                {
                    DateTime endData = Convert.ToDateTime(endTime);
                    table_fwzt = "zm_building_fwzt_" + endData.Year;
                    if (Convert.ToDateTime(endTime) > DateTime.Now)
                    {
                        table_fwzt = "zm_building_fwzt_" + DateTime.Now.Year;
                    }
                }
                else
                {
                    DateTime endData = Convert.ToDateTime(today);
                    table_fwzt = "zm_building_fwzt_" + endData.Year;
                }
                if (string.IsNullOrEmpty(startTime) && string.IsNullOrEmpty(endTime))
                {
                    startTime = "2024-01-01";
                    endTime = DateTime.Now.ToString("yyyy-MM-dd");
                }
                if (startTime == "2024-01-01" && endTime == today)
                {
                    sql = @"select ROW_NUMBER() OVER ()index ,* from (select t1.*,t2.""orderIdx"" from pro_hoursejbxx_gq('" + startTime + "', ' " + endTime + " 23:59:59', 'zm_pc_base', 'zm_pc_yh','" + table_fwzt + "') t1 left join zm_dic_xzq t2 on t1.xzqdm=t2.xzqdm order by t2.\"orderIdx\" ) t where 1=1 " + strWhere + " ";
                }
                else
                {
                    sql = @"select ROW_NUMBER() OVER ()index ,* from (select t1.*,t2.""orderIdx"" from pro_hoursejbxx_gq('" + startTime + "','" + endTime + " 23:59:59', 'zm_pc_baselog', 'zm_pc_yhlog','" + table_fwzt + "') t1 left join zm_dic_xzq t2 on t1.xzqdm=t2.xzqdm order by t2.\"orderIdx\" ) t where 1=1 " + strWhere + "  ";
                }
                var data = _dbContext.Database.GetDbConnection().Query<object>(sql).ToList();
                IWorkbook wb = new XSSFWorkbook();
                ISheet sheet = wb.CreateSheet("各区经营性自建房基本信息情况");
                // 第一行 标题
                IRow row = sheet.CreateRow(0);
                row.CreateCell(0).SetCellValue("序号");
                row.CreateCell(1).SetCellValue("区");
                row.CreateCell(2).SetCellValue("房屋总量");
                row.CreateCell(3).SetCellValue("居住人数");
                row.CreateCell(4).SetCellValue("租住人数");
                row.CreateCell(5).SetCellValue("房间数量");
                row.CreateCell(6).SetCellValue("出租房间数量");
                row.CreateCell(7).SetCellValue("一层房屋栋数");
                row.CreateCell(8).SetCellValue("二层房屋栋数");
                row.CreateCell(9).SetCellValue("三层及以上房屋栋数");
                // 第二行 写数据
                int i = 1;
                foreach (object obj in data)
                {
                    Dictionary<string, object> dictionary = new Dictionary<string, object>();
                    if (obj != null)
                    {

                        var properties = obj as IDictionary<string, object>;
                        row = sheet.CreateRow(i);
                        foreach (var kvp in properties)
                        {
                            //var val = kvp.Value+"";
                            if (kvp.Key == "index")
                            {
                                row.CreateCell(0).SetCellValue(i);
                            }
                            else if (kvp.Key == "xzq")
                            {
                                row.CreateCell(1).SetCellValue(kvp.Value + "");
                            }
                            else if (kvp.Key == "zcount")
                            {
                                row.CreateCell(2).SetCellValue(kvp.Value + "");
                            }
                            else if (kvp.Key == "jzrks")
                            {
                                row.CreateCell(3).SetCellValue(kvp.Value + "");
                            }
                            else if (kvp.Key == "zzrs")
                            {
                                row.CreateCell(4).SetCellValue(kvp.Value + "");
                            }
                            else if (kvp.Key == "fjsl")
                            {
                                row.CreateCell(5).SetCellValue(kvp.Value + "");
                            }
                            else if (kvp.Key == "jzfjs")
                            {
                                row.CreateCell(6).SetCellValue(kvp.Value + "");
                            }
                            else if (kvp.Key == "one")
                            {
                                row.CreateCell(7).SetCellValue(kvp.Value + "");
                            }
                            else if (kvp.Key == "two")
                            {
                                row.CreateCell(8).SetCellValue(kvp.Value + "");
                            }
                            else if (kvp.Key == "three")
                            {
                                row.CreateCell(9).SetCellValue(kvp.Value + "");
                            }
                        }

                        i++;
                    }
                }

                // 写 WorkBook信息到 内存流中
                byte[] buffer = null;
                using (MemoryStream ms = new MemoryStream())
                {
                    wb.Write(ms);
                    buffer = ms.ToArray();
                }
                return File(buffer, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", "各区经营性自建房基本信息情况" + DateTime.Now.ToString("yyyyMMdd") + ".xlsx");
            }
            catch (Exception ex)
            {
                throw;
            }
        }

        /// <summary>
        /// 导出基本信息统计明细（乡镇）
        /// </summary>
        /// <param name="pageRequest">type check</param>
        /// <returns></returns>
        [HttpGet("basicHouse/export/basicHouseTownListExport")]
        [Produces("application/octet-stream")]
        //[ProducesResponseType(200)]
        //[ProducesResponseType(401)]
        public IActionResult basicHouseTownListExport([FromQuery] string xzqdm, [FromQuery] string startTime, [FromQuery] string endTime, [FromQuery] string cked)
        {
            try
            {

                var userNumber = this.CurrentUser();
                string strWhere = "";
                var user = _userRepository.FirstOrDefault(u => u.Number == userNumber && u.IsDelete != 1);
                string district = user.District;
                var options = new JsonSerializerOptions();
                options.PropertyNameCaseInsensitive = true; // 设置属性不区分大小写
                Xzq model = System.Text.Json.JsonSerializer.Deserialize<Xzq>(district, options);


                if (model != null)
                {
                    if (user.RoleType == 1 || user.RoleType == 2)
                    {
                        strWhere += "and  xzq!='东城区' and xzq!='西城区'";
                    }
                    else if (user.RoleType == 3) //区级
                    {
                        strWhere += " and xzqdm='" + model.RegionCode + "'";
                    }
                    else if (user.RoleType == 4) //区级
                    {
                        strWhere += " and  towndm='" + model.RegionCode + "'";
                    }
                }
                string today = DateTime.Now.ToString("yyyy-MM-dd");
                string sql = "";
                if (string.IsNullOrEmpty(startTime) && string.IsNullOrEmpty(endTime))
                {
                    startTime = "2024-01-01";
                    endTime = DateTime.Now.ToString("yyyy-MM-dd");
                }
                if (!string.IsNullOrEmpty(xzqdm))
                {
                    strWhere += " and xzqdm='" + xzqdm + "'";
                }
                string table_fwzt = "";
                if (!string.IsNullOrEmpty(endTime))
                {
                    DateTime endData = Convert.ToDateTime(endTime);
                    table_fwzt = "zm_building_fwzt_" + endData.Year;
                }
                else
                {
                    DateTime endData = Convert.ToDateTime(today);
                    table_fwzt = "zm_building_fwzt_" + endData.Year;
                }
                if (startTime == "2024-01-01" && endTime == today)
                {
                    sql = @"select ROW_NUMBER() OVER ()index ,* from (select * from pro_hoursejbxx_xz('" + startTime + "', ' " + endTime + " 23:59:59', 'zm_pc_base', 'zm_pc_yh','" + table_fwzt + "') order by towndm) t  where 1=1 " + strWhere + "  ";
                }
                else
                {
                    sql = @"select ROW_NUMBER() OVER ()index ,* from (select * from pro_hoursejbxx_xz('" + startTime + "','" + endTime + " 23:59:59', 'zm_pc_baselog', 'zm_pc_yhlog','" + table_fwzt + "') order by towndm) t  where 1=1 " + strWhere + "  ";
                }

                var data = _dbContext.Database.GetDbConnection().Query<object>(sql).ToList();
                IWorkbook wb = new XSSFWorkbook();
                ISheet sheet = wb.CreateSheet("乡镇经营性自建房基本信息情况");
                // 第一行 标题
                IRow row = sheet.CreateRow(0);
                row.CreateCell(0).SetCellValue("序号");
                row.CreateCell(1).SetCellValue("区");
                row.CreateCell(2).SetCellValue("乡镇");
                row.CreateCell(3).SetCellValue("房屋总量");
                row.CreateCell(4).SetCellValue("居住人数");
                row.CreateCell(5).SetCellValue("租住人数");
                row.CreateCell(6).SetCellValue("房间数量");
                row.CreateCell(7).SetCellValue("出租房间数量");
                row.CreateCell(8).SetCellValue("一层房屋栋数");
                row.CreateCell(9).SetCellValue("二层房屋栋数");
                row.CreateCell(10).SetCellValue("三层及以上房屋栋数");
                // 第二行 写数据
                int i = 1;
                foreach (object obj in data)
                {
                    Dictionary<string, object> dictionary = new Dictionary<string, object>();
                    if (obj != null)
                    {

                        var properties = obj as IDictionary<string, object>;
                        row = sheet.CreateRow(i);
                        foreach (var kvp in properties)
                        {
                            //var val = kvp.Value+"";
                            if (kvp.Key == "index")
                            {
                                row.CreateCell(0).SetCellValue(i);
                            }
                            else if (kvp.Key == "xzq")
                            {
                                row.CreateCell(1).SetCellValue(kvp.Value + "");
                            }
                            else if (kvp.Key == "town")
                            {
                                row.CreateCell(2).SetCellValue(kvp.Value + "");
                            }
                            else if (kvp.Key == "zcount")
                            {
                                row.CreateCell(3).SetCellValue(kvp.Value + "");
                            }
                            else if (kvp.Key == "jzrks")
                            {
                                row.CreateCell(4).SetCellValue(kvp.Value + "");
                            }
                            else if (kvp.Key == "zzrs")
                            {
                                row.CreateCell(5).SetCellValue(kvp.Value + "");
                            }
                            else if (kvp.Key == "fjsl")
                            {
                                row.CreateCell(6).SetCellValue(kvp.Value + "");
                            }
                            else if (kvp.Key == "jzfjs")
                            {
                                row.CreateCell(7).SetCellValue(kvp.Value + "");
                            }
                            else if (kvp.Key == "one")
                            {
                                row.CreateCell(8).SetCellValue(kvp.Value + "");
                            }
                            else if (kvp.Key == "two")
                            {
                                row.CreateCell(9).SetCellValue(kvp.Value + "");
                            }
                            else if (kvp.Key == "three")
                            {
                                row.CreateCell(10).SetCellValue(kvp.Value + "");
                            }
                        }

                        i++;
                    }
                }

                // 写 WorkBook信息到 内存流中
                byte[] buffer = null;
                using (MemoryStream ms = new MemoryStream())
                {
                    wb.Write(ms);
                    buffer = ms.ToArray();
                }
                return File(buffer, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", "乡镇经营性自建房基本信息情况" + DateTime.Now.ToString("yyyyMMdd") + ".xlsx");
            }
            catch (Exception ex)
            {
                throw;
            }
        }

        /// <summary>
        /// 导出基本信息统计明细（各村）
        /// </summary>
        /// <param name="pageRequest">type check</param>
        /// <returns></returns>
        [HttpGet("basicHouse/export/basicHouseVillageListExport")]
        [Produces("application/octet-stream")]
        //[ProducesResponseType(200)]
        //[ProducesResponseType(401)]
        public IActionResult basicHouseVillageListExport([FromQuery] string xzqdm, [FromQuery] string towndm, [FromQuery] string startTime, [FromQuery] string endTime, [FromQuery] string cked)
        {
            try
            {

                var userNumber = this.CurrentUser();
                string strWhere = "";
                var user = _userRepository.FirstOrDefault(u => u.Number == userNumber && u.IsDelete != 1);
                string district = user.District;
                var options = new JsonSerializerOptions();
                options.PropertyNameCaseInsensitive = true; // 设置属性不区分大小写
                Xzq model = System.Text.Json.JsonSerializer.Deserialize<Xzq>(district, options);


                if (model != null)
                {
                    if (user.RoleType == 1 || user.RoleType == 2)
                    {
                        strWhere += "and  xzq!='东城区' and xzq!='西城区'";
                    }
                    else if (user.RoleType == 3) //区级
                    {
                        strWhere += " and xzqdm='" + model.RegionCode + "'";
                    }
                    else if (user.RoleType == 4) //区级
                    {
                        strWhere += " and  towndm='" + model.RegionCode + "'";
                    }
                }
                string today = DateTime.Now.ToString("yyyy-MM-dd");
                string sql = "";
                if (string.IsNullOrEmpty(startTime) && string.IsNullOrEmpty(endTime))
                {
                    startTime = "2024-01-01";
                    endTime = DateTime.Now.ToString("yyyy-MM-dd");
                }
                if (!string.IsNullOrEmpty(xzqdm))
                {
                    strWhere += " and xzqdm='" + xzqdm + "'";
                }
                if (!string.IsNullOrEmpty(towndm))
                {
                    strWhere += " and towndm='" + towndm + "'";
                }
                string sheetName = "村级经营性自建房基本信息情况";
                string table_fwzt = "";
                if (!string.IsNullOrEmpty(endTime))
                {
                    DateTime endData = Convert.ToDateTime(endTime);
                    table_fwzt = "zm_building_fwzt_" + endData.Year;
                    if (Convert.ToDateTime(endTime) > DateTime.Now)
                    {
                        table_fwzt = "zm_building_fwzt_" + DateTime.Now.Year;
                    }
                }
                else
                {
                    DateTime endData = Convert.ToDateTime(today);
                    table_fwzt = "zm_building_fwzt_" + endData.Year;
                }
                if (startTime == "2024-01-01" && endTime == today)
                {
                    sql = @"select ROW_NUMBER() OVER ()index ,* from (select * from pro_hoursejbxx_cj('" + startTime + "', ' " + endTime + " 23:59:59', 'zm_pc_base', 'zm_pc_yh','" + table_fwzt + "') order by towndm) t  where 1=1 " + strWhere + "  ";
                }
                else
                {
                    sql = @"select ROW_NUMBER() OVER ()index ,* from (select * from pro_hoursejbxx_cj('" + startTime + "','" + endTime + " 23:59:59', 'zm_pc_baselog', 'zm_pc_yhlog','" + table_fwzt + "') order by towndm) t  where 1=1 " + strWhere + "  ";
                }

                if (cked == "true")
                {
                    sheetName = "重点村经营性自建房基本信息情况";
                    string basetable = "zm_pc_baselog";
                    string yhtable = "zm_pc_yhlog";
                    if (startTime == "2024-01-01" && endTime == today)
                    {
                        basetable = "zm_pc_base";
                        yhtable = "zm_pc_yh";
                    }
                    sql = "select ROW_NUMBER() OVER ()index ,* from(select m2.villagedm,m2.xzq,m2.xzqdm,m2.towndm,m2.town,m2.village,COALESCE(zcount,0)zcount,COALESCE(jzrks,0)jzrks,COALESCE(zzrs,0)zzrs,COALESCE(fjsl,0) fjsl,COALESCE(jzfjs,0)jzfjs," +
                     "COALESCE(one,0)one,COALESCE(two,0)two,COALESCE(three,0)three from (select t1.villagedm,t2.xzq,t2.xzqdm,t2.towndm,t2.town,zcount,jzrks,zzrs,fjsl,jzfjs,one,two,three from zdcmd t1 left join" +
                     " ( select ROW_NUMBER() OVER ()index ,* from (select * from pro_hoursejbxx_cj('" + startTime + "', '" + endTime + " 23:59:59', '" + basetable + "', '" + yhtable + "','" + table_fwzt + "') order by towndm) t  " +
                     "where 1=1  and villagedm in(select villagedm from  zdcmd)) t2 on t1.villagedm=t2.villagedm) m1,zm_building_ledger m2 where m1.villagedm=m2.villagedm ) t";
                }

                var data = _dbContext.Database.GetDbConnection().Query<object>(sql).ToList();
                IWorkbook wb = new XSSFWorkbook();
                ISheet sheet = wb.CreateSheet(sheetName);
                // 第一行 标题
                IRow row = sheet.CreateRow(0);
                row.CreateCell(0).SetCellValue("序号");
                row.CreateCell(1).SetCellValue("区");
                row.CreateCell(2).SetCellValue("乡镇");
                row.CreateCell(3).SetCellValue("村");
                row.CreateCell(4).SetCellValue("房屋总量");
                row.CreateCell(5).SetCellValue("居住人数");
                row.CreateCell(6).SetCellValue("租住人数");
                row.CreateCell(7).SetCellValue("房间数量");
                row.CreateCell(8).SetCellValue("出租房间数量");
                row.CreateCell(9).SetCellValue("一层房屋栋数");
                row.CreateCell(10).SetCellValue("二层房屋栋数");
                row.CreateCell(11).SetCellValue("三层及以上房屋栋数");

                // 第二行 写数据
                int i = 1;
                foreach (object obj in data)
                {
                    Dictionary<string, object> dictionary = new Dictionary<string, object>();
                    if (obj != null)
                    {

                        var properties = obj as IDictionary<string, object>;
                        row = sheet.CreateRow(i);
                        foreach (var kvp in properties)
                        {
                            //var val = kvp.Value+"";
                            if (kvp.Key == "index")
                            {
                                row.CreateCell(0).SetCellValue(i);
                            }
                            else if (kvp.Key == "xzq")
                            {
                                row.CreateCell(1).SetCellValue(kvp.Value + "");
                            }
                            else if (kvp.Key == "town")
                            {
                                row.CreateCell(2).SetCellValue(kvp.Value + "");
                            }
                            else if (kvp.Key == "village")
                            {
                                row.CreateCell(3).SetCellValue(kvp.Value + "");
                            }
                            else if (kvp.Key == "zcount")
                            {
                                row.CreateCell(4).SetCellValue(kvp.Value + "");
                            }
                            else if (kvp.Key == "jzrks")
                            {
                                row.CreateCell(5).SetCellValue(kvp.Value + "");
                            }
                            else if (kvp.Key == "zzrs")
                            {
                                row.CreateCell(6).SetCellValue(kvp.Value + "");
                            }
                            else if (kvp.Key == "fjsl")
                            {
                                row.CreateCell(7).SetCellValue(kvp.Value + "");
                            }
                            else if (kvp.Key == "jzfjs")
                            {
                                row.CreateCell(8).SetCellValue(kvp.Value + "");
                            }
                            else if (kvp.Key == "one")
                            {
                                row.CreateCell(9).SetCellValue(kvp.Value + "");
                            }
                            else if (kvp.Key == "two")
                            {
                                row.CreateCell(10).SetCellValue(kvp.Value + "");
                            }
                            else if (kvp.Key == "three")
                            {
                                row.CreateCell(11).SetCellValue(kvp.Value + "");
                            }
                        }

                        i++;
                    }
                }

                // 写 WorkBook信息到 内存流中
                byte[] buffer = null;
                using (MemoryStream ms = new MemoryStream())
                {
                    wb.Write(ms);
                    buffer = ms.ToArray();
                }
                return File(buffer, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", sheetName + DateTime.Now.ToString("yyyyMMdd") + ".xlsx");
            }
            catch (Exception ex)
            {
                throw;
            }
        }

    }
}
